package com.databasefunction;
import java.awt.List;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.UUID;

import org.omg.CORBA.DynAnyPackage.Invalid;

import com.mysql.jdbc.Statement;


public class DtabaseServices {

	static DatabaseConnection database_connection;
	static String conn=null;
	static Connection connection;
	
	public static void main(String[] args) {
	
	database_connection=new DatabaseConnection();
	
	
	connection=database_connection.getConnection(null);
	database_connection.createDatabase(connection);
	//database_connection.selectDatabase(connection);
	
	
	createTableUsers();
	//insertTableUsers("afgh","d","ad","ss","5","ggbbb","gghgf","gsdfg","yh","gg","5t",567890);
	//getrole("afgh","d");
	//isuserexist("afgh");
	//isuserexist("ddd");
	//securitytype("afgh");
	//securitytype("sdfghj");
	
	connection=database_connection.getConnection(conn);
	database_connection.createDatabase(connection);
	
	
	createTableAddDealers();
	
	//submitdata("romit","customer","A/6 jape estate","jogeshwari","mumbai","maharashtra","india","400223","8446660107","roshanJoshi@igse.com",123456789);
	//getaddress("romit");
	//getCustmer("ro","customer");
	createTableBarcodeFinal();
	//deleteUser("roshan");
	//saveCredentials("afgh","roshan","pass");
//	getrole("ddd", "dddd");
//	createTableAnalysis();
	//addAnalysisTodb("a2we434567yt");
	//addAnalysisTodb("123456789");
	//updateAnalysisTodb("a2we434567yt");
	//deleteAnalysisfromDB("a2we434567yt");
	//getAnalysisList();

	
	
	}
	
	//////database tables and functions of admin their role security questions starts here...............
	
   public static void createTableUsers(){
	  String dbName = "parvati_database"; 
	 connection=  database_connection.getConnection(dbName);
	 String url=DatabaseConstants.DB_URL;
		Statement stmt = null;
	   try {
		stmt = (Statement) connection.createStatement();
		System.out.println("statement start");
		   String sql = "CREATE TABLE IF NOT EXISTS userInfo" +
                   "("+DatabaseConstants.userName+" VARCHAR(255), " +
                   " "+DatabaseConstants.password+" TEXT, " + 
                   " "+DatabaseConstants.role+" TEXT, " +
                   " "+DatabaseConstants.fullName+" TEXT, " +
                   " "+DatabaseConstants.address+" TEXT, " +
                   " "+DatabaseConstants.contact+" TEXT, " +
                   " "+DatabaseConstants.emailId+" TEXT, " +
                   " "+DatabaseConstants.securityQuestion1+" TEXT, " +
                   " "+DatabaseConstants.securityAnswer1+" TEXT, " +
                   " "+DatabaseConstants.securityQuestion2+" TEXT, " +
                   " "+DatabaseConstants.securityAnswer2+" TEXT, " +
                   " "+DatabaseConstants.date+" INT,"+
                   " CONSTRAINT unique_id_constraint UNIQUE ("+DatabaseConstants.userName+")) "; 
		   	System.out.println(sql);
      stmt.executeUpdate(sql);
		System.out.println("table check");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}//end finally try
	      
	   
	
   }
   
   
   public static boolean insertTableUsers(String uName,String password,String role, String fullName, String address, String contact,
		   String emailId, String securityQuestion1, String securityAns1, String securityQuestion2, String securityAns2, int date){
	   boolean res=true;
	   int response=0;
	   System.out.println(uName);
		 
		Statement stmt = null;
		try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "INSERT INTO userInfo " +
	                   "VALUES ('"+uName+"','"+password+"','"+role+"','"+fullName+"','"+address+"','"+contact+"','"+emailId+"','"+securityQuestion1+"','"+securityAns1+"','"+securityQuestion2+"','"+securityAns2+"',"+date+")";
			System.out.println(sql);
			response=stmt.executeUpdate(sql);
			if (response==0) {
				res=false;
			}
	      System.out.println("data inserted successfully");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			res=false;
			
			e.printStackTrace();
			return res;
		}
		return res;
   }
   public static String getrole(String user,String pass){
	   String role="";
	   Statement stmt = null;
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select role from userInfo where userName = '"+user+"' and password = '"+pass+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			
			
		      //STEP 5: Extract data from result set
		      while(rs.next()){
		         //Retrieve by column name
		        
		         role = rs.getString(DatabaseConstants.role);
		         System.out.print(", role: " + role);
		       
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return "Invalid";
			
		}
	   	  System.out.println(role); 
	return role;
}
   public static boolean isuserexist(String user){
	   boolean isuserexists=false;
	   Statement stmt = null;
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from userInfo where userName = '"+user+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		    
			
			while(rs.next()){
		         //Retrieve by column name
		        
		        isuserexists=true;
		       
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
			
		}
	   	   System.out.println(isuserexists);
	return isuserexists;
}
   
   public static List getAllUserList(){
	   List list=new List();
	   Statement stmt=null;
		  try {
				stmt = (Statement) connection.createStatement();
				
				String sql = "select * from userInfo";
				System.out.println(sql);
				ResultSet rs = stmt.executeQuery(sql);
			      //STEP 5: Extract data from result set
			    
				
				while(rs.next()){
			         //Retrieve by column name
					list.add(rs.getString(DatabaseConstants.userName));
			       
			      }
			      rs.close();
			} catch (Exception e) {
				e.printStackTrace();
				return list;
				
			}
		for (int i = 0; i < list.getItemCount(); i++) {
			System.out.println(list.getItem(i)+" ");	
		}
	   
	return list;
   }
   
   public static boolean deleteUser(String user){
	   boolean dataBool=false;
	   int response=0;
	   Statement stmt=null;
		  try {
				stmt = (Statement) connection.createStatement();
				
				String sql = "DELETE FROM userInfo where "+DatabaseConstants.userName+" = '"+user+"'";
				System.out.println(sql);
				response= stmt.executeUpdate(sql);
				 if (response==0) {
					dataBool=false;
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				dataBool=false;
				return dataBool;
				
			}
		System.out.println(dataBool);
	   return dataBool;
	   
   }
   
   
   public boolean addUser(String user,String pass,String role){
	   boolean dataBool=false;
	   int response=0;
	   Statement stmt=null;
	   
		try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "INSERT INTO userInfo " +
	                   "VALUES ('"+user+"','"+pass+"','"+role+"','','','','','','','','',0)";
			System.out.println(sql);
			response=stmt.executeUpdate(sql);
			if (response==0) {
				dataBool=false;
			}
	      System.out.println("data inserted successfully");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			dataBool=false;
			
			e.printStackTrace();
			return dataBool;
		}
	   
	   return dataBool;
	   
   } 
   
   public static HashMap securitytype(String user){
	   HashMap hm = new HashMap();
	   Statement stmt = null;
	   String securityQuestion1="";
	   String securityQuestion2="";
	   String securityanswer1="";
	   String securityanswer2="";
	   
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from userInfo where userName = '"+user+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		    
			
			while(rs.next()){
		         //Retrieve by column name
		        securityQuestion1=rs.getString(DatabaseConstants.securityQuestion1);
		        securityanswer1 = rs.getString(DatabaseConstants.securityAnswer1);
		        securityQuestion2=rs.getString(DatabaseConstants.securityQuestion2);
		        securityanswer2 = rs.getString(DatabaseConstants.securityAnswer2);
		       hm.put("question1", securityQuestion1);
		       hm.put("ans1", securityanswer1);
		       hm.put("question2", securityQuestion2);
		       hm.put("ans2", securityanswer2);
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return hm;
			
		}
	   	   
	return hm;
}  
   
   
   public static boolean saveCredentials(String originaluser,String newuser,String password){
		boolean dataBool = true;
		int response=0;
		 Statement stmt = null;
		   
		   try {
				stmt = (Statement) connection.createStatement();
				
				String sql = "UPDATE userInfo " +
						"SET "+DatabaseConstants.userName+ " ='"+newuser+"', "+DatabaseConstants.password+" = '"+password+"'" +
								" where "+DatabaseConstants.userName+" = '"+originaluser+"'";
				System.out.println(sql);
			response=stmt.executeUpdate(sql);
			      //STEP 5: Extract data from result set
			    
				if (response==0) {
					dataBool=false;
				}
				
			      
			} catch (Exception e) {
				e.printStackTrace();
				dataBool= false;
				System.out.println("asdfgh"+dataBool);
				return dataBool;
				
			}
		   	   System.out.println(dataBool);
		return dataBool;
	}

   
   
   
////database tables and functions of admin their role security questions ends here...............
   
////database tables and functions for broker commision agent and customer starts here ...............
   public static void createTableAddDealers(){
	     String dbname = "parvati_database";
		 connection=  database_connection.getConnection(dbname);
		 
			Statement stmt = null;
		   try {
			stmt = (Statement) connection.createStatement();
			System.out.println("statement start");
			   String sql = "CREATE TABLE IF NOT EXISTS dealerInfo" +
	                   "("+DatabaseConstants.dealername+" VARCHAR(255) UNIQUE, " +
	                   " "+DatabaseConstants.dealerrole+" TEXT, " + 
	                   " "+DatabaseConstants.dealerUniqueid+" VARCHAR(255), " +
	                   " "+DatabaseConstants.dealeraddress+" TEXT, " +
	                   " "+DatabaseConstants.dealercity+" TEXT, " +
	                   " "+DatabaseConstants.dealerdistrict+" TEXT, " +
	                   " "+DatabaseConstants.dealerstate+" TEXT, " +
	                   " "+DatabaseConstants.dealercountry+" TEXT, " +
	                   " "+DatabaseConstants.dealerzipcode+" TEXT, " +
	                   " "+DatabaseConstants.dealercontact+" TEXT, " +
	                   " "+DatabaseConstants.dealeremailid+" TEXT, " +
	                   " "+DatabaseConstants.dealerdate+" INT,"+
	                   " CONSTRAINT unique_id_constraint PRIMARY KEY ("+DatabaseConstants.dealerUniqueid+")) "; 
			   	System.out.println(sql);
	      stmt.executeUpdate(sql);
			System.out.println("table check");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("error");
			e.printStackTrace();
		}//end finally try
		      
		   
		
	   }
	   
   public static String getUniqueId() {
	
	String uniqueId = UUID.randomUUID().toString().replaceAll("-", "").substring(0, 6);
	
	if(isUniqueIdPresent(uniqueId)){
		
		uniqueId=getUniqueId();
	}
	
	return uniqueId;
}
   
   
   private static boolean isUniqueIdPresent(String uniqueId) {
	   Statement stmt1 = null;
	   boolean res=false;
	   try {
			
			stmt1=(Statement) connection.createStatement();
			
			String sqlquery = "select "+DatabaseConstants.dealerUniqueid+" from dealerInfo";
			System.out.println(sqlquery);
			ResultSet rs = stmt1.executeQuery(sqlquery);
		      //STEP 5: Extract data from result set
		      while(rs.next()){
		         
		     if(uniqueId==rs.getNString(DatabaseConstants.dealerUniqueid)){
		    	 
		    	 res=true;
		     }
		        
		      }
		      rs.close();
	   }catch (Exception e) {
			// TODO Auto-generated catch block
			res=false;
			e.printStackTrace();
			return res;
		}
	return res;
}

public static boolean submitdata(String name,String role,String address,String city,String district,String state,String country,String zipcode,String contact,String emailid,int date){
	
	   boolean res=true;
	  int response=0;
		 
		Statement stmt = null;
		String uniqueId="";
		
			
				
				try {
					stmt = (Statement) connection.createStatement();
					uniqueId = getUniqueId();
					
					if(uniqueId!=""){
						
						String sql = "INSERT INTO dealerInfo " +
				                   "VALUES ('"+name+"','"+role+"','"+uniqueId+"','"+address+"','"+city+"','"+district+"','"+state+"','"+country+"','"+zipcode+"','"+contact+"','"+emailid+"',"+date+")";
						System.out.println(sql);
						response=stmt.executeUpdate(sql);
						if (response==0) {
							res=false;
						}
						
				     // System.out.println("data inserted successfully");
						
					}
					
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					res=false;
					System.out.println(res);
					return false;
				}
		return res;
	   
	   
   }
  
public static String getaddress(String user){
	   String address="";
	   
	   Statement stmt = null;
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from dealerInfo where "+DatabaseConstants.dealername+" = '"+user+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		      while(rs.next()){
		         //Retrieve by column name
		        
		    	  address = rs.getString(DatabaseConstants.address)+","+rs.getString(DatabaseConstants.dealercity)+", "+rs.getString(DatabaseConstants.dealerdistrict)+", "+rs.getString(DatabaseConstants.dealerstate)+".";
		         
		         System.out.print(", address: " + address);
		       
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return "Invalid";
			
		}
	   	   
	return address;
}  

public static List getCustmer(String initials,String role){

	List list = new List();
	Statement stmt=null;
	  try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from dealerInfo where "+DatabaseConstants.dealername+" LIKE '"+initials+"%' and "+DatabaseConstants.dealerrole+" = '"+role+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		    
			
			while(rs.next()){
		         //Retrieve by column name
				list.add(rs.getString(DatabaseConstants.dealername));
		       
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return list;
			
		}
	for (int i = 0; i < list.getItemCount(); i++) {
		System.out.println(list.getItem(i)+" ");	
	}
	
	return list;
	
}
   
 //database tables and functions for broker commision agent and customer ends here ............... 
 //DATABASE FOR barcodeFinalTable functions starts here................................

public static void createTableBarcodeFinal(){
    String dbname = "parvati_database";
	 connection=  database_connection.getConnection(dbname);
	 
		Statement stmt = null;
	   try {
		stmt = (Statement) connection.createStatement();
		System.out.println("statement start");
		   String sql = "CREATE TABLE IF NOT EXISTS barcodeFinal" +
                  "("+DatabaseConstants.barcodeNumber+" VARCHAR(255), " +
                  " "+DatabaseConstants.barcodeBrokerName+" TEXT, " + 
                  " "+DatabaseConstants.barcodecustomerName+" TEXT, " +
                  " "+DatabaseConstants.barcodeCommisionAgentName+" TEXT, " +
                  " "+DatabaseConstants.barcodeRstNo+" TEXT, " +
                  " "+DatabaseConstants.barcodeVehicleNo+" TEXT, " +
                  " "+DatabaseConstants.barcodeCommodity+" TEXT, " +
                  " "+DatabaseConstants.barcodeNoOfBags+" TEXT, " +
                  " "+DatabaseConstants.barcodePONo+" TEXT, " +
                  " "+DatabaseConstants.barcodeGrossWeight+" TEXT, " +
                  " "+DatabaseConstants.barcodeTareWeight+" TEXT, " +
                  " "+DatabaseConstants.barcodeNetWeight+" TEXT,"+
                  " "+DatabaseConstants.barcodedate+" INT, " + 
                  " "+DatabaseConstants.barcodeDamageSeeds+" TEXT, " +
                  " "+DatabaseConstants.barcodeSandSilica+" TEXT, " +
                  " "+DatabaseConstants.barcodeMoisture+" TEXT, " +
                  " "+DatabaseConstants.barcodeExcessDamage+" TEXT, " +
                  " "+DatabaseConstants.barcodeExcessSandSilica+" TEXT, " +
                  " "+DatabaseConstants.barcodeExcessMoisture+" TEXT, " +
                  " "+DatabaseConstants.barcodeDamageAmountCut+" TEXT, " +
                  " "+DatabaseConstants.barcodeSandSilicaAmountCut+" TEXT, " +
                  " "+DatabaseConstants.barcodeMoistureAmountCut+" TEXT, " +
				  " "+DatabaseConstants.barcodeRate+" TEXT, " +
                  " "+DatabaseConstants.barcodeGrossAmount+" TEXT, " +
                  " "+DatabaseConstants.barcodeLessRebate+" TEXT, " +
                  " "+DatabaseConstants.barcodeUnloading+" TEXT, " +
                  " "+DatabaseConstants.barcodecashDiscount+" TEXT, " +
                  " "+DatabaseConstants.barcodeAdvance+" TEXT,"+
                  " "+DatabaseConstants.barcodeAddDalali+" TEXT, " + 
                  " "+DatabaseConstants.barcodeTotalRebate+" TEXT, " +
                  " "+DatabaseConstants.barcodeTotalPaid+" TEXT, " +
                  " "+DatabaseConstants.barcodeHide+" TEXT, " +
                  " "+DatabaseConstants.barcodeWeighingMachineUser+" TEXT, " +
                  " "+DatabaseConstants.barcodeLabUser+" TEXT, " +
                  " CONSTRAINT barcode_constraint PRIMARY KEY ("+DatabaseConstants.barcodeNumber+")) "; 
		   	System.out.println(sql);
     stmt.executeUpdate(sql);
		System.out.println("table check barcodeFinal");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("error");
		e.printStackTrace();
	}//end finally try
	      
	   
	
  }


public static boolean isBarcodeExists(String code){
	boolean isBarcodeExists=false;
	 Statement stmt = null;
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from barcodeFinal where "+DatabaseConstants.barcodeNumber+" = '"+code+"'";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		    
			
			while(rs.next()){
		         //Retrieve by column name
		        
		        isBarcodeExists=true;
		       
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return isBarcodeExists;
			
		}
	   	   System.out.println(isBarcodeExists);
	
	return isBarcodeExists;
}


public static HashMap getDataAgainstSearch(String searchText,String serchBy,int startDate,int endDate, String searchDealer,String serchDealerBusiness){
	
	HashMap hm = new HashMap();
	HashMap hm1= new HashMap();
	HashMap hm2= new HashMap();
	boolean isDateFieldNull=true;
	Statement stmt = null;
	Statement stmt1 = null;
	ResultSet rs=null;
	ResultSet rs1=null;
	String sql="";
	String sql1="";
	
	if (startDate==0 && endDate==0) {
		isDateFieldNull=false;
	}
	
		   try {
				stmt = (Statement) connection.createStatement();
				sql = "select * from dealerInfo where "+searchDealer+" = '"+searchText+"'";
				System.out.println(sql);
				rs = stmt.executeQuery(sql);
			      //STEP 5: Extract data from result set
			      while(rs.next()){
			         //Retrieve by column name
			        
			    	 hm.put("dataName",rs.getString(DatabaseConstants.dealername));
			    	 hm.put("dataUniqueId", rs.getString(DatabaseConstants.dealerUniqueid));
			    	 hm.put("dataAddress", rs.getString(DatabaseConstants.dealeraddress));
			    	 hm.put("dataCity", rs.getString(DatabaseConstants.dealercity));
			    	 hm.put("dataDistrict", rs.getString(DatabaseConstants.dealerdistrict));
			    	 hm.put("dataState", rs.getString(DatabaseConstants.dealerstate));
			    	 hm.put("dataCountry", rs.getString(DatabaseConstants.dealercountry));
			    	 hm.put("dataZipcode", rs.getString(DatabaseConstants.dealerzipcode));
			    	 hm.put("dataContact", rs.getString(DatabaseConstants.dealercontact));
			    	 hm.put("dataEmailId", rs.getString(DatabaseConstants.dealeremailid)); 
			    	 
			    	 hm2.put("dealerData",hm);
			       
			      }
			      rs.close();
			      
			      if (!isDateFieldNull) {
			    	  stmt1= (Statement) connection.createStatement();
				      sql1 = "select * from barcodeFinal where "+serchDealerBusiness+" = '"+searchText+"'";
						System.out.println(sql1);
						rs1 = stmt.executeQuery(sql1);
				}else if(startDate==0){
					
					
					 stmt1= (Statement) connection.createStatement();
				      sql1 = "select * from barcodeFinal where "+serchDealerBusiness+" = '"+searchText+"' " +
				      		"AND "+DatabaseConstants.barcodedate+" <= "+endDate+"";
						System.out.println(sql1);
						rs1 = stmt.executeQuery(sql1);
					
				}else if(endDate==0){
					
					stmt1= (Statement) connection.createStatement();
				      sql1 = "select * from barcodeFinal where "+serchDealerBusiness+" = '"+searchText+"' " +
				      		"AND "+DatabaseConstants.barcodedate+" >= "+startDate+"";
						System.out.println(sql1);
						rs1 = stmt.executeQuery(sql1);
					
				}else{
					stmt1= (Statement) connection.createStatement();
				      sql1 = "select * from barcodeFinal where "+serchDealerBusiness+" = '"+searchText+"' " +
				      		"AND ("+DatabaseConstants.barcodedate+" BETWEEN "+startDate+" AND "+endDate+"";
						System.out.println(sql1);
						rs1 = stmt.executeQuery(sql1);
					
					
					
				}
			     
				      //STEP 5: Extract data from result set
				      while(rs1.next()){
				         //Retrieve by column name
				    	  hm1.put("businessBrokerName", rs1.getString(DatabaseConstants.barcodeBrokerName));
					    	 hm1.put("businesscustomerName", rs1.getString(DatabaseConstants.barcodecustomerName));
					    	 hm1.put("businessCommisionAgentName", rs1.getString(DatabaseConstants.barcodeCommisionAgentName));
				    	 hm1.put("businessRSTNo",rs1.getString(DatabaseConstants.barcodeRstNo));
				    	 hm1.put("businessVehicleNo", rs1.getString(DatabaseConstants.barcodeVehicleNo));
				    	 hm1.put("businessCommodity", rs1.getString(DatabaseConstants.barcodeCommodity));
				    	 hm1.put("businessNoOfBags", rs1.getString(DatabaseConstants.barcodeNoOfBags));
				    	 hm1.put("businessPONo", rs1.getString(DatabaseConstants.barcodePONo));
				    	 hm1.put("businessGrossWeight", rs1.getString(DatabaseConstants.barcodeGrossWeight));
				    	 hm1.put("businessTareWeight", rs1.getString(DatabaseConstants.barcodeTareWeight));
				    	 hm1.put("businessNetWeight", rs1.getString(DatabaseConstants.barcodeNetWeight));
				    	 hm1.put("businessdate", rs1.getInt(DatabaseConstants.barcodedate));
				    	 
				    	 hm1.put("businessDamageSeeds", rs1.getString(DatabaseConstants.barcodeDamageSeeds));
				    	 hm1.put("businessSandSilica", rs1.getString(DatabaseConstants.barcodeSandSilica));
				    	 hm1.put("businessMoisture", rs1.getString(DatabaseConstants.barcodeMoisture));
				    	 hm1.put("businessExcessDamage", rs1.getString(DatabaseConstants.barcodeExcessDamage));
				    	 hm1.put("businessExcessSandSilica", rs1.getString(DatabaseConstants.barcodeExcessSandSilica));
				    	 hm1.put("businessExcessMoisture", rs1.getString(DatabaseConstants.barcodeExcessMoisture));
				    	 hm1.put("businessDamageAmountCut", rs1.getString(DatabaseConstants.barcodeDamageAmountCut));
				    	 hm1.put("businessSandSilicaAmountCut", rs1.getString(DatabaseConstants.barcodeSandSilicaAmountCut));
				    	 hm1.put("businessMoistureAmountCut", rs1.getString(DatabaseConstants.barcodeMoistureAmountCut));
				    	 hm1.put("businessUnloading", rs1.getString(DatabaseConstants.barcodeUnloading));
				    	 hm1.put("businessAddDalali", rs1.getString(DatabaseConstants.barcodeAddDalali));
				    	 hm1.put("businessHide", rs1.getString(DatabaseConstants.barcodeHide));
				    	 
				    	 
				    	 hm1.put("businessRate", rs1.getString(DatabaseConstants.barcodeRate));
				    	 hm1.put("businessGrossAmount", rs1.getString(DatabaseConstants.barcodeGrossAmount));
				    	 hm1.put("businessLessRebate", rs1.getString(DatabaseConstants.barcodeLessRebate));
				    	 hm1.put("businesscashDiscount", rs1.getString(DatabaseConstants.barcodecashDiscount));
				    	 hm1.put("businessAdvance", rs1.getString(DatabaseConstants.barcodeAdvance)); 
				    	 
				    	 hm1.put("businessTotalRebate", rs1.getString(DatabaseConstants.barcodeTotalRebate));
				    	 hm1.put("businessTotalPaid", rs1.getString(DatabaseConstants.barcodeTotalPaid));
				    	 hm1.put("businessWeighingMachineUser", rs1.getString(DatabaseConstants.barcodeWeighingMachineUser));
				    	 hm1.put("businessLabUser", rs1.getString(DatabaseConstants.barcodecashDiscount));
				    	  
				       hm2.put(rs1.getString(DatabaseConstants.barcodeNumber), hm1);
				      }
				      rs1.close();
			      
			} catch (Exception e) {
				e.printStackTrace();
				return null;
				
			}
		   
		   
		   
	
	return hm2;
}





public static HashMap getDealerData(String searchText,String serchBy,int startDate,int endDate){
	HashMap hm = new HashMap();
	
	boolean isDateFieldNull=true;
	
	
	
	
	switch (serchBy) {
	case "brokername":
		
	hm=	getDataAgainstSearch(searchText,serchBy,startDate,endDate,DatabaseConstants.dealername,DatabaseConstants.barcodeBrokerName);
		  
		
		break;
		
	case "commisionagentname":
	hm=	getDataAgainstSearch(searchText,serchBy,startDate,endDate,DatabaseConstants.dealername,DatabaseConstants.barcodeCommisionAgentName);
		break;
		
case "customername":
	hm=getDataAgainstSearch(searchText,serchBy,startDate,endDate,DatabaseConstants.dealername,DatabaseConstants.barcodecustomerName);
		break;
		
case "city":
	
	hm=getDataAgainstCityDistrict(searchText,serchBy,startDate,endDate,"city");
	
	break;
case "district":
	hm=getDataAgainstCityDistrict(searchText,serchBy,startDate,endDate,"district");
	break;

	default:
		break;
	}
	
	
	
	return hm;
	
	
}

private static HashMap getDataAgainstCityDistrict(String searchText,
		String serchBy, int startDate, int endDate, String string) {
	// TODO Auto-generated method stub
	HashMap hm = new HashMap();
	HashMap hm1= new HashMap();
	HashMap hm2= new HashMap();
	boolean isDateFieldNull=true;
	Statement stmt = null;
	Statement stmt1 = null;
	ResultSet rs=null;
	ResultSet rs1=null;
	String sql="";
	String sql1="";
	
	
	List list=new List();
	
	if (startDate==0 && endDate==0) {
		isDateFieldNull=false;
	}
	
		   try {
			   
			   if (string =="city") {
					stmt = (Statement) connection.createStatement();
					sql = "select * from dealerInfo where "+DatabaseConstants.dealercity+" = '"+searchText+"'";
					System.out.println(sql);
					rs = stmt.executeQuery(sql);
			}else{
				
				stmt = (Statement) connection.createStatement();
				sql = "select * from dealerInfo where "+DatabaseConstants.dealerdistrict+" = '"+searchText+"'";
				System.out.println(sql);
				rs = stmt.executeQuery(sql);
			}
			   
			
			      //STEP 5: Extract data from result set
			      while(rs.next()){
			         //Retrieve by column name
			        list.add(rs.getString(DatabaseConstants.dealername));
			    	 
			    	 
			    	 hm2.put("dealerData",null);
			       
			      }
			      rs.close();
			      for (int i = 0; i < list.getItemCount(); i++) {
			    	  if (!isDateFieldNull) {
				    	  stmt1= (Statement) connection.createStatement();
					      sql1 = "select * from barcodeFinal where "+DatabaseConstants.barcodeBrokerName+" = '"+list.getItem(i)+"' " +
					      		"OR "+DatabaseConstants.barcodeCommisionAgentName+" = '"+list.getItem(i)+"' OR " +
					      				""+DatabaseConstants.barcodecustomerName+" = '"+list.getItem(i)+"'";
							System.out.println(sql1);
							rs1 = stmt.executeQuery(sql1);
					}else if(startDate==0){
						
						
						 stmt1= (Statement) connection.createStatement();
					      sql1 = "select * from barcodeFinal where "+DatabaseConstants.barcodedate+" <= "+endDate+"" +
					      		" AND ("+DatabaseConstants.barcodeBrokerName+" = '"+list.getItem(i)+"' " +
					      		"OR "+DatabaseConstants.barcodeCommisionAgentName+" = '"+list.getItem(i)+"' OR " +
					      				""+DatabaseConstants.barcodecustomerName+" = '"+list.getItem(i)+"')";
							System.out.println(sql1);
							rs1 = stmt.executeQuery(sql1);
						
					}else if(endDate==0){
						
						 stmt1= (Statement) connection.createStatement();
					      sql1 = "select * from barcodeFinal where "+DatabaseConstants.barcodedate+" >= "+startDate+"" +
					      		" AND ("+DatabaseConstants.barcodeBrokerName+" = '"+list.getItem(i)+"' " +
					      		"OR "+DatabaseConstants.barcodeCommisionAgentName+" = '"+list.getItem(i)+"' OR " +
					      				""+DatabaseConstants.barcodecustomerName+" = '"+list.getItem(i)+"')";
							System.out.println(sql1);
							rs1 = stmt.executeQuery(sql1);
						
					}else{
						
						stmt1= (Statement) connection.createStatement();
					      sql1 = "select * from barcodeFinal where ("+DatabaseConstants.barcodedate+" BETWEEN "+startDate+" AND "+endDate+")" +
					      		" AND ("+DatabaseConstants.barcodeBrokerName+" = '"+list.getItem(i)+"' " +
					      		"OR "+DatabaseConstants.barcodeCommisionAgentName+" = '"+list.getItem(i)+"' OR " +
					      				""+DatabaseConstants.barcodecustomerName+" = '"+list.getItem(i)+"')";
							System.out.println(sql1);
							rs1 = stmt.executeQuery(sql1);
						
						
						
					}
				     
					      //STEP 5: Extract data from result set
					      while(rs1.next()){
					         //Retrieve by column name
					    	  hm1.put("businessBrokerName", rs1.getString(DatabaseConstants.barcodeBrokerName));
						    	 hm1.put("businesscustomerName", rs1.getString(DatabaseConstants.barcodecustomerName));
						    	 hm1.put("businessCommisionAgentName", rs1.getString(DatabaseConstants.barcodeCommisionAgentName));
					    	 hm1.put("businessRSTNo",rs1.getString(DatabaseConstants.barcodeRstNo));
					    	 hm1.put("businessVehicleNo", rs1.getString(DatabaseConstants.barcodeVehicleNo));
					    	 hm1.put("businessCommodity", rs1.getString(DatabaseConstants.barcodeCommodity));
					    	 hm1.put("businessNoOfBags", rs1.getString(DatabaseConstants.barcodeNoOfBags));
					    	 hm1.put("businessPONo", rs1.getString(DatabaseConstants.barcodePONo));
					    	 hm1.put("businessGrossWeight", rs1.getString(DatabaseConstants.barcodeGrossWeight));
					    	 hm1.put("businessTareWeight", rs1.getString(DatabaseConstants.barcodeTareWeight));
					    	 hm1.put("businessNetWeight", rs1.getString(DatabaseConstants.barcodeNetWeight));
					    	 hm1.put("businessdate", rs1.getInt(DatabaseConstants.barcodedate));
					    	 
					    	 hm1.put("businessDamageSeeds", rs1.getString(DatabaseConstants.barcodeDamageSeeds));
					    	 hm1.put("businessSandSilica", rs1.getString(DatabaseConstants.barcodeSandSilica));
					    	 hm1.put("businessMoisture", rs1.getString(DatabaseConstants.barcodeMoisture));
					    	 hm1.put("businessExcessDamage", rs1.getString(DatabaseConstants.barcodeExcessDamage));
					    	 hm1.put("businessExcessSandSilica", rs1.getString(DatabaseConstants.barcodeExcessSandSilica));
					    	 hm1.put("businessExcessMoisture", rs1.getString(DatabaseConstants.barcodeExcessMoisture));
					    	 hm1.put("businessDamageAmountCut", rs1.getString(DatabaseConstants.barcodeDamageAmountCut));
					    	 hm1.put("businessSandSilicaAmountCut", rs1.getString(DatabaseConstants.barcodeSandSilicaAmountCut));
					    	 hm1.put("businessMoistureAmountCut", rs1.getString(DatabaseConstants.barcodeMoistureAmountCut));
					    	 hm1.put("businessUnloading", rs1.getString(DatabaseConstants.barcodeUnloading));
					    	 hm1.put("businessAddDalali", rs1.getString(DatabaseConstants.barcodeAddDalali));
					    	 hm1.put("businessHide", rs1.getString(DatabaseConstants.barcodeHide));
					    	 
					    	 
					    	 hm1.put("businessRate", rs1.getString(DatabaseConstants.barcodeRate));
					    	 hm1.put("businessGrossAmount", rs1.getString(DatabaseConstants.barcodeGrossAmount));
					    	 hm1.put("businessLessRebate", rs1.getString(DatabaseConstants.barcodeLessRebate));
					    	 hm1.put("businesscashDiscount", rs1.getString(DatabaseConstants.barcodecashDiscount));
					    	 hm1.put("businessAdvance", rs1.getString(DatabaseConstants.barcodeAdvance)); 
					    	 
					    	 hm1.put("businessTotalRebate", rs1.getString(DatabaseConstants.barcodeTotalRebate));
					    	 hm1.put("businessTotalPaid", rs1.getString(DatabaseConstants.barcodeTotalPaid));
					    	 hm1.put("businessWeighingMachineUser", rs1.getString(DatabaseConstants.barcodeWeighingMachineUser));
					    	 hm1.put("businessLabUser", rs1.getString(DatabaseConstants.barcodecashDiscount));
					    	  
					       hm2.put(rs1.getString(DatabaseConstants.barcodeNumber), hm1);
					      }
					      rs1.close();
				}
			     
			      
			} catch (Exception e) {
				e.printStackTrace();
				return null;
				
			}
		   
		   
		   
	
	return hm2;
	
	
	
	
	
	
}

public static void changeBusinessHidden(String barcode,String status){
	boolean dataBool=true;
	int response=0;
	Statement stmt = null;
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "UPDATE barcodeFinal " +
					"SET "+DatabaseConstants.barcodeHide+ " ='"+status+"'" +
							" where "+DatabaseConstants.barcodeNumber+" = '"+barcode+"'";
			System.out.println(sql);
			response=stmt.executeUpdate(sql);
		      //STEP 5: Extract data from result set
		    
			if (response==0) {
				dataBool=false;
			}
			
		      
		} catch (Exception e) {
			e.printStackTrace();
			dataBool= false;
			System.out.println(dataBool);
			
			
		}
	   	   System.out.println(dataBool);
	
}

public boolean update(String barcode,String brockername, String customername, String commitionagent,String businessRSTNo,String businessVehicle,String businessCommodity,String businessNoOfBags,String businessPONo,String businessGrossWeight,String businessTareWeight,String businessNetWeight,
		int businessdate,String businessRate,String businessGrossAmount,String businessLessRebate,String businesscashDiscount,String businessAdvance,String businessTotalRebate,String businessTotalPaid,String businessWeighingMachineUser,
		String businessLabUser,String businessDamageSeeds,String businessSandSilica,String businessMoisture,String businessExcessDamage,String businessExcessSandSilica,String businessExcessMoisture,String businessDamageAmountCut,
		String businessSandSilicaAmountCut,String businessMoistureAmountCut,String businessUnloading,String businessAddDalali,String hide,int operation){
	boolean dataBool=true;
	
	Statement stmt = null;
	int response=0,responseInsert=0;
	Statement stmtInsert=null;
	if(operation==DatabaseConstants.operation_update){
		try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "UPDATE barcodeFinal " +
					"SET "+DatabaseConstants.barcodeBrokerName+ " ='"+brockername+"', "+DatabaseConstants.barcodecustomerName+" ='"+customername+"'," +
							""+DatabaseConstants.barcodeCommisionAgentName+ " ='"+commitionagent+"', "+DatabaseConstants.barcodeRstNo+" ='"+businessRSTNo+"', "+DatabaseConstants.barcodeVehicleNo+" = '"+businessVehicle+"', " +
							""+DatabaseConstants.barcodeCommodity+ " ='"+businessCommodity+"', "+DatabaseConstants.barcodeNoOfBags+" ='"+businessNoOfBags+"', " +
							""+DatabaseConstants.barcodePONo+ " ='"+businessPONo+"', "+DatabaseConstants.barcodeGrossWeight+" ='"+businessTareWeight+"', " +
							""+DatabaseConstants.barcodeNetWeight+ " ='"+businessNetWeight+"', "+DatabaseConstants.barcodedate+" ="+businessdate+", " +
							""+DatabaseConstants.barcodeRate+ " ='"+businessRate+"', "+DatabaseConstants.barcodeGrossAmount+" ='"+businessGrossAmount+"', " +
							""+DatabaseConstants.barcodeLessRebate+ " ='"+businessLessRebate+"', "+DatabaseConstants.barcodecashDiscount+" ='"+businesscashDiscount+"', " +
							""+DatabaseConstants.barcodeAdvance+ " ='"+businessAdvance+"', "+DatabaseConstants.barcodeTotalRebate+" ='"+businessTotalRebate+"', " +
							""+DatabaseConstants.barcodeTotalPaid+ " ='"+businessTotalPaid+"', "+DatabaseConstants.barcodeWeighingMachineUser+" ='"+businessWeighingMachineUser+"', " +
							""+DatabaseConstants.barcodeLabUser+ " ='"+businessLabUser+"', "+DatabaseConstants.barcodeDamageSeeds+" ='"+businessDamageSeeds+"', " +
							""+DatabaseConstants.barcodeSandSilica+ " ='"+businessSandSilica+"', "+DatabaseConstants.barcodeMoisture+" ='"+businessMoisture+"', " +
							""+DatabaseConstants.barcodeExcessDamage+ " ='"+businessExcessDamage+"', "+DatabaseConstants.barcodeExcessSandSilica+" ='"+businessExcessSandSilica+"', " +
							""+DatabaseConstants.barcodeExcessMoisture+ " ='"+businessExcessMoisture+"', "+DatabaseConstants.barcodeDamageAmountCut+" ='"+businessDamageAmountCut+"', " +
							""+DatabaseConstants.barcodeSandSilicaAmountCut+ " ='"+businessSandSilicaAmountCut+"', "+DatabaseConstants.barcodeMoistureAmountCut+" ='"+businessMoistureAmountCut+"', " +
							""+DatabaseConstants.barcodeUnloading+ " ='"+businessUnloading+"', "+DatabaseConstants.barcodeAddDalali+" ='"+businessAddDalali+"'" +
							" where "+DatabaseConstants.barcodeNumber+" = '"+barcode+"'";
			System.out.println(sql);
		response=stmt.executeUpdate(sql);
		      //STEP 5: Extract data from result set
		    if (response==0) {
				dataBool=false;
			}
			
			
		      
		} catch (Exception e) {
			e.printStackTrace();
			dataBool= false;
			System.out.println(dataBool);
			return dataBool;
			
		}
	   	   System.out.println(dataBool);
	   	   return dataBool;
		
	}else{
		
		try {
			stmtInsert = (Statement) connection.createStatement();
			
			String sql = "INSERT INTO userInfo " +
	                   "VALUES ('"+barcode+"','"+brockername+"','"+customername+"','"+commitionagent+"','"+businessRSTNo+"','"+businessVehicle+"','"+businessCommodity+"'," +
	                   		"'"+businessNoOfBags+"','"+businessPONo+"','"+businessGrossWeight+"','"+businessTareWeight+"','"+businessNetWeight+"'" +
	                   		""+businessdate+",'"+businessDamageSeeds+"','"+businessSandSilica+"','"+businessMoisture+"','"+businessExcessDamage+"'" +
	                   		"'"+businessExcessSandSilica+"','"+businessExcessMoisture+"','"+businessDamageAmountCut+"','"+businessSandSilicaAmountCut+"','"+businessMoistureAmountCut+"'" +
	                   		"'"+businessRate+"','"+businessGrossAmount+"','"+businessLessRebate+"','"+businessUnloading+"','"+businesscashDiscount+"'" +
	                   		"'"+businessAdvance+"','"+businessAddDalali+"','"+businessTotalRebate+"','"+businessTotalPaid+"','"+hide+"'" +
	                   		"'"+businessWeighingMachineUser+"','"+businessLabUser+"'" +
	                   		")";
			System.out.println(sql);
			responseInsert=stmtInsert.executeUpdate(sql);
		      //STEP 5: Extract data from result set
		    if (responseInsert==0) {
				dataBool=false;
			}  
			
		} catch (Exception e) {
			e.printStackTrace();
			dataBool= false;
			System.out.println(dataBool);
			return dataBool;
			
		}
	   	   System.out.println(dataBool);
	   	   return dataBool;
		
	}
	   
	   



}

 
public static boolean  addValue(String barcode,String excessdamage,String excesssandsilica,String excessmoisture,String labuser) {
	
	boolean dataBool=true;
	int response=0;
	Statement stmt = null;
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "UPDATE barcodeFinal " +
					"SET "+DatabaseConstants.barcodeExcessDamage+ " ='"+excessdamage+"', "+DatabaseConstants.barcodeExcessSandSilica+" = '"+excesssandsilica+"'," +
							""+DatabaseConstants.barcodeExcessMoisture+" = '"+excessmoisture+"', "+DatabaseConstants.barcodeLabUser+" = '"+labuser+"'" +
							" where "+DatabaseConstants.barcodeNumber+" = '"+barcode+"'";
			System.out.println(sql);
		 response=stmt.executeUpdate(sql);
		      //STEP 5: Extract data from result set
		      
		 if (response==0) {
			dataBool=false;
		}
		 
		} catch (Exception e) {
			e.printStackTrace();
			dataBool= false;
			System.out.println(dataBool);
			return dataBool;
			
			
		}
	   	   System.out.println(dataBool);
	return dataBool;
	
}

//DATABASE FOR barcodeFinalTable functions ends here................................  

//cOMMON FUNCTIONS FOR ALL DATABASE STARTS HERE.......................................




//cOMMON FUNCTIONS FOR ALL DATABASE ENDS HERE.......................................
//analysisreporttable and functions starts here..............

public static void createTableAnalysis(){
	  String dbName = "parvati_database"; 
	 connection=  database_connection.getConnection(dbName);
	 String url=DatabaseConstants.DB_URL;
		Statement stmt = null;
	   try {
		stmt = (Statement) connection.createStatement();
		System.out.println("statement start");
		   String sql = "CREATE TABLE IF NOT EXISTS analysisReportTable" +
                 "("+DatabaseConstants.analysisBarcode+" VARCHAR(255), " +
                 " "+DatabaseConstants.analysisAvailable+" TEXT, " +
                 " CONSTRAINT barcode_constraint UNIQUE ("+DatabaseConstants.analysisBarcode+")) "; 
		   	System.out.println(sql);
    stmt.executeUpdate(sql);
		System.out.println("table check analysis");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}//end finally try
 }


public static HashMap getAnalysisList(){
	HashMap hm = new HashMap();
	   Statement stmt = null;
	   
	   
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "select * from analysisReportTable";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
		      //STEP 5: Extract data from result set
		    
			
			while(rs.next()){
		         //Retrieve by column name
		       hm.put( rs.getString(DatabaseConstants.analysisBarcode),  rs.getString(DatabaseConstants.analysisAvailable));
		       System.out.println("get the availability :"+hm.get(rs.getString(DatabaseConstants.analysisBarcode)));
		      }
		      rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			return hm;
			
		}
	   	   
	  
	   
	return hm;
}

public static boolean deleteAnalysisfromDB(String barcode){
	  boolean dataBool=false;
	   int response=0;
	   Statement stmt=null;
		  try {
				stmt = (Statement) connection.createStatement();
				
				String sql = "DELETE FROM analysisReportTable where "+DatabaseConstants.analysisBarcode+" = '"+barcode+"'";
				System.out.println(sql);
				response= stmt.executeUpdate(sql);
				 if (response==0) {
					dataBool=false;
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				dataBool=false;
				return dataBool;
				
			}
		System.out.println(dataBool);
	   return dataBool;
	
	
}
public static boolean updateAnalysisTodb(String barcode){
	boolean dataBool = true;
	int response=0;
	 Statement stmt = null;
	   
	   try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "UPDATE analysisReportTable " +
					"SET "+DatabaseConstants.analysisAvailable+ " ='Y'" +
							" where "+DatabaseConstants.analysisBarcode+" = '"+barcode+"'";
			System.out.println(sql);
		response=stmt.executeUpdate(sql);
		      //STEP 5: Extract data from result set
		    
			if (response==0) {
				dataBool=false;
			}
			
		      
		} catch (Exception e) {
			e.printStackTrace();
			dataBool= false;
			System.out.println("asdfgh"+dataBool);
			return dataBool;
			
		}
	   	   System.out.println(dataBool);
	return dataBool;
	
	
}
public static boolean addAnalysisTodb(String barcode){
	 boolean res=true;
	   int response=0;
	   		 
		Statement stmt = null;
		try {
			stmt = (Statement) connection.createStatement();
			
			String sql = "INSERT INTO analysisReportTable " +
	                   "VALUES ('"+barcode+"','N')";
			System.out.println(sql);
			response=stmt.executeUpdate(sql);
			if (response==0) {
				res=false;
			}
	      System.out.println("data inserted successfully");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			res=false;
			
			e.printStackTrace();
			return res;
		}
		return res;
	
	
}
//analysisreporttable and functions ends here..............
}
